﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Dashboard.Expense.Business
{
    public class Exp_ExchangeRate : BusinessBase
    {
        public DataTable GetExchangeRateOfYear(int year)
        {
            string sql = @"
            SELECT a.pvalue1 as currency,b.year,b.month,b.rate 
              from EXP_Parameter a left join exp_exchangeRate b
                    on a.pvalue1=b.fromcurrency and b.year={0}
                    where a.ptype='Currency'
                    order by a.pvalue1,b.year,b.month";

            sql = string.Format(sql, year);

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GenerateDataSourceForExchangeRate(int year)
        {
            DataTable final = new DataTable();
            final.Columns.Add("Currency", typeof(string));
            for (int i = 1; i <= 12; i++)
            {
                final.Columns.Add(i.ToString(), typeof(decimal));
            }

            final.AcceptChanges();

            DataTable rates = this.GetExchangeRateOfYear(year);

            DataTable currency = rates.DefaultView.ToTable(true, "Currency");

            foreach (DataRow row in currency.Rows)
            {
                final.Rows.Add(row["Currency"].ToString(), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
            }

            foreach (DataRow row in final.Rows)
            {
                for (int i = 1; i <= 12; i++)
                {
                    DataRow[] rows = rates.Select("Currency = '" + row["Currency"].ToString() + "' AND month=" + i);
                    if (rows.Length == 1)
                    {
                        row[i] = rows[0]["rate"];
                    }
                }
            }

            final.AcceptChanges();
            return final;
        }

        public void SaveExchangeRates(Dictionary<string, Dictionary<int, decimal>> rates, int year, string currentUser)
        {
            foreach (var currency in rates.Keys)
            {
                var dict = rates[currency];

                foreach (var month in dict.Keys)
                {
                    string sql = "select * from exp_exchangerate where year={0} and month={1} and fromcurrency='{2}'";
                    sql = string.Format(sql, year, month, currency);

                    DataTable tmp = DbConnection.DataSet(sql).Tables[0];
                    int count = tmp.Rows.Count;
                    if (count > 0)
                    {
                        // update
                        sql = "update exp_exchangerate set rate={0},updatedby='{1}',updateddate=getdate() where id={2}";
                        sql = string.Format(sql, dict[month], currentUser, tmp.Rows[0]["ID"].ToString());

                        DbConnection.ExecuteSql(sql);
                    }
                    else
                    {
                        // insert
                        sql = @"insert into exp_exchangerate(year,month,fromcurrency,tocurrency,rate,insertby,insertdate,updatedby,updateddate) 
                                values({0},{1},'{2}','{3}',{4},'{5}',getdate(),'{6}',getdate())";
                        sql = string.Format(sql, year, month, currency, base.GetBaseCurrency(), dict[month], currentUser, currentUser);

                        DbConnection.ExecuteSql(sql);
                    }
                }
            }
        }

        public decimal GetExchangeRate(string fromCurrency, string toCurrency)
        {
            if (fromCurrency == toCurrency)
            {
                return 1;
            }
            int year = DateTime.Now.Year;
            int month = DateTime.Now.Month;

            string oriSQL = "select * from exp_exchangerate where year={0} and month={1} and fromcurrency='{2}' and tocurrency='{3}'";
            string sql = string.Format(oriSQL, year, month, fromCurrency, toCurrency);
            DataTable rate = DbConnection.DataSet(sql).Tables[0];

            if (rate.Rows.Count == 0)
            {
                year = DateTime.Now.AddMonths(-1).Year;
                month = DateTime.Now.AddMonths(-1).Month;

                sql = string.Format(oriSQL, year, month, fromCurrency, toCurrency);
                rate = DbConnection.DataSet(sql).Tables[0];
                if (rate.Rows.Count == 0)
                {
                    return 0;
                }
                else
                {
                    return decimal.Parse(rate.Rows[0]["Rate"].ToString());
                }
            }
            else
            {
                return decimal.Parse(rate.Rows[0]["Rate"].ToString());
            }
        }
    }
}
